﻿-- Работа с таблицами типа Tree
CREATE PROCEDURE [dbo].[axs_axaSecurity_Tree]
	@cTableName [varchar](40),  -- Наименование таблици
	@OpID [int], -- Тип операций
	@CodeID [int], -- код записы
	@Newmgrid [int] -- Новый менеджер (код)
WITH EXECUTE AS CALLER
AS
DECLARE @dynsql nvarchar(4000)

SET @dynsql =
		CASE @OpId 
		WHEN 1 THEN 
	'DELETE FROM ' + @cTableName + char(13)+
	'WHERE hrccrt LIKE (SELECT hrccrt FROM ' + @cTableName + char(13)+
	'	WHERE CodeID = '+convert(varchar,@CodeID)+') + ''%'''
		WHEN 2 THEN 
		'UPDATE E'+char(13)+
		'SET mgrid = '+convert(varchar,@Newmgrid)+char(13)+
		'FROM '+@cTableName+' AS E JOIN '+@cTableName+' AS M'+char(13)+
		'  ON E.mgrid = M.CodeID'+char(13)+
		'WHERE M.CodeID = '+convert(varchar,@CodeID)+char(13)+
		'DELETE FROM '+@cTableName+char(13)+
		'WHERE CodeID = '+convert(varchar,@CodeID)
		WHEN 3 THEN 
		'UPDATE E'+char(13)+
		'SET mgrid = M.mgrid'+char(13)+
		'FROM '+@cTableName+' AS E JOIN '+@cTableName+' AS M'+char(13)+
		'  ON E.mgrid = M.CodeID'+char(13)+
		'WHERE M.CodeID = '+convert(varchar,@CodeID)+char(13)+
		'DELETE FROM '+@cTableName+char(13)+
		'WHERE CodeID = '+ convert(varchar,@CodeID)
		WHEN 4 THEN 
	'SELECT * FROM ' + @cTableName + char(13)+
	'WHERE hrccrt LIKE (SELECT hrccrt FROM ' + @cTableName + char(13)+
	'	WHERE CodeID = '+convert(varchar,@CodeID)+') + ''%'''
  END 
	EXEC(@dynsql)


